package com.easyjava.builder;

import com.easyjava.bean.Constants;
import com.easyjava.bean.FieldInfo;
import com.easyjava.bean.TableInfo;
import com.easyjava.utils.JsonUtils;
import com.easyjava.utils.PropertiesUtils;
import com.easyjava.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BuildTable {
    private static Connection conn = null;
    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);

    private static String SQL_SHOW_TABLE_STATUS = "show table status";

    private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";

    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url,username,password);

        } catch (Exception e) {
            logger.error("数据库连接失败",e);
        }
    }

    public static List<TableInfo> getTables(){
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()){
                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");
//                logger.info("tableName:{},comment:{}",tableName,comment);

                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);
                }

                beanName = processField(beanName,true);
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

                readFiledInfo(tableInfo);

                getKeyIndexInfo(tableInfo);
//                logger.info("tableInfo:{}", JsonUtils.convertObj2Json(tableInfo));
                tableInfoList.add(tableInfo);
            }
        }catch(Exception e){
            logger.error("读取表失败",e);
        }finally {
            if (tableResult != null){
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }if (ps !=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return tableInfoList;
    }


    /**
     *
     * @param tableinfo
     * @return
     */
    private static void readFiledInfo(TableInfo tableinfo){
        PreparedStatement ps = null;
        ResultSet filedResult = null;
        List<FieldInfo> filedInfoList = new ArrayList<>();

        List<FieldInfo> fieldExtendList = new ArrayList<>();
        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableinfo.getTableName()));
            filedResult = ps.executeQuery();

            Boolean haveDateTime = false;
            Boolean haveDate = null;
            Boolean haveBigDecimal = false;
            while (filedResult.next()){
                String field = filedResult.getString("field");
                String type = filedResult.getString("type");
                String extra = filedResult.getString("extra");
                String comment = filedResult.getString("comment");

                if (type.indexOf("(")>0) {
                    type = type.substring(0,type.indexOf("("));
                }
                String propertyName = processField(field,false);

                FieldInfo fieldInfo = new FieldInfo();
                filedInfoList.add(fieldInfo);

                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setIsAutoIncrement("auto_increment".equalsIgnoreCase(extra) ? true : false);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type)){
                    haveDateTime = true;
                }
                if (ArrayUtils.contains((Constants.SQL_DECIMAL_TYPES),type)){
                    haveBigDecimal = true;
                }
                if (ArrayUtils.contains((Constants.SQL_DATE_TYPES),type)){
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPES,type)){
                    FieldInfo fuzzyField = new FieldInfo();
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    fuzzyField.setFieldName(fieldInfo.getFieldName());
                    fuzzyField.setSqlType(type);
                    fieldExtendList.add(fuzzyField);

                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type)||ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)){

                    FieldInfo timeStartField = new FieldInfo();
                    timeStartField.setJavaType("String");
                    timeStartField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    timeStartField.setFieldName(fieldInfo.getFieldName());
                    timeStartField.setSqlType(type);
                    fieldExtendList.add(timeStartField);

                    FieldInfo timeEndField = new FieldInfo();
                    timeEndField.setJavaType("String");
                    timeEndField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    timeEndField.setFieldName(fieldInfo.getFieldName());
                    timeEndField.setSqlType(type);
                    fieldExtendList.add(timeEndField);
                }
            }
            tableinfo.setHaveDate(haveDate);
            tableinfo.setHaveDateTime(haveDateTime);
            tableinfo.setHaveBigDecimal(haveBigDecimal);
            tableinfo.setFieldList(filedInfoList);
            tableinfo.setFieldExtendList(fieldExtendList);
        }catch(Exception e){
            logger.error("读取表失败",e);
        }finally {
            if (filedResult != null){
                try {
                    filedResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }if (ps !=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 读取索引
     * @param tableinfo
     * @return
     */
    private static List<FieldInfo> getKeyIndexInfo(TableInfo tableinfo){
        PreparedStatement ps = null;
        ResultSet filedResult = null;
        List<FieldInfo> filedInfoList = new ArrayList<>();

        Map<String,FieldInfo> tempMap = new HashMap<>();
        for (FieldInfo fieldInfo:tableinfo.getFieldList()){
            tempMap.put(fieldInfo.getFieldName(),fieldInfo);
        }
        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableinfo.getTableName()));
            filedResult = ps.executeQuery();
            while (filedResult.next()){
                String keyName = filedResult.getString("key_name");
                Integer nonUnique = filedResult.getInt("non_unique");
                String columnName = filedResult.getString("column_name");
                if (nonUnique == 1){
                    continue;
                }
                List<FieldInfo> keyFieldList = tableinfo.getKeyIndexMap().get(keyName);
                if (null == keyFieldList){
                    keyFieldList = new ArrayList<>();
                    tableinfo.getKeyIndexMap().put(keyName,keyFieldList);
                    logger.info("---keyName:{},keyFieldList:{}",keyName,JsonUtils.convertObj2Json(tableinfo.getKeyIndexMap()));
                }
//                for (FieldInfo fieldInfo:tableinfo.getFieldList()){
//                    if (fieldInfo.getFieldName().equals(columnName)){
//                        keyFieldList.add(fieldInfo);
//                    }
//                }
                keyFieldList.add(tempMap.get(columnName));
//                logger.info("keyFieldList:{}",JsonUtils.convertObj2Json(tableinfo.getFieldList()));

            }
        }catch(Exception e){
            logger.error("读取索引失败",e);
        }finally {
            if (filedResult != null){
                try {
                    filedResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }if (ps !=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return filedInfoList;
    }

    /**
     * 表字段解析
     * @param filed 字段
     * @param uperCaseFirstLetter 字段第一个字母是否大写
     * @return
     */
    private static String processField(String filed,Boolean uperCaseFirstLetter){
        StringBuffer sb = new StringBuffer();
        String[] fields = filed.split("_");
        sb.append(uperCaseFirstLetter? StringUtils.uperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i=1;i< fields.length;i++){
            sb.append(StringUtils.uperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    private static String processJavaType(String type){
        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPES,type)){
            return "Integer";
        }else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES,type)){
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_STRING_TYPES,type)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TYPES,type) || ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,type)) {
            return "BigDecimal";
        }else {
            throw new RuntimeException("无法识别的类型："  + type);
        }
    }

}
